package service;

import db.DBUtil;
import model.Grade;

import java.sql.*;

public class StudentManger {


    //添加学生
    public void addStudent(String student_no, String name, String major) {
        String sql = "insert into student(student_no,name,major) values(?,?,?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)
        ) {
            ps.setString(1, student_no);
            ps.setString(2, name);
            ps.setString(3, major);

            int i = ps.executeUpdate();

            if (i > 0) {
                System.out.println("学生创建成功！" + name);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


    //添加课程

    public void addCourse(String course_no, String name, int credit) {

        String sql = "insert into course(course_no,name,credit) values(?,?,?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, course_no);
            ps.setString(2, name);
            ps.setInt(3, credit);

            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("课程添加成功！" + name);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


    //添加成绩
    public void addGrade(Grade grade) {

        String sql =  "insert into grade(student_no,course_no,usual_score,final_score,total_score) values(?,?,?,?,?)";
        try(Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);){

            ps.setString(1,grade.getStudentNo());
            ps.setString(2,grade.getCourseNo());
            ps.setDouble(3,grade.getUsualScore());
            ps.setDouble(4,grade.getFinalScore());
            ps.setDouble(5,grade.getTotalScore());

            int i = ps.executeUpdate();

            if (i > 0) {
                System.out.println("成功添加成绩");
            }


        } catch (Exception e) {
            throw new RuntimeException(e);
        }




    }


    //查询学生成绩
    public void report(String student_no) {

        String sql = "select s.name student_name,c.name course_name,usual_score,final_score,total_score " +
                "from grade g " +
                "join student s on g.student_no = s.student_no " +
                "join course c on g.course_no = c.course_no " +
                "where g.student_no = ?";

        System.out.println(sql);

        System.out.println("");

        try(Connection conn = DBUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ){

            ps.setString(1, student_no);
            ResultSet rs = ps.executeQuery();

            //ps.executeQuery();

            while(rs.next()){
                String sname = rs.getString("student_name");
                String cname = rs.getString("course_name");
                double usual_score = rs.getDouble("usual_score");
                double final_score=rs.getDouble("final_score");
                double total_score=rs.getDouble("total_score");

                System.out.println("学生姓名：" + sname + ",课程：" + cname + ",平时成绩：" + usual_score + ",期末成绩：" + final_score + "，总评成绩：" + total_score  );

            }





        } catch (SQLException e) {
            e.printStackTrace();
        }



    }


}
